package com.cmnit.analysis.dao

import com.cmnit.analysis.common.{TDao, TSql}
import org.apache.log4j.Logger
import org.apache.spark.sql.DataFrame

class TrafficInsideDao extends TSql with TDao {
  private val logger: Logger = Logger.getLogger(this.getClass)

  /**
   * 获取省内出口流量（结果表）
   *
   * @return
   */
  def trafficInside(enTime: String, exTime: String, acctDate: String): DataFrame = {
    sparkSQL(
      " SELECT " +
        " a.entollstationid enid, " +
        " a.tollstationname enname, " +
        " 'station' entype, " +
        " b.extollstationid stationid, " +
        " b.tollstationname stationname, " +
        " b.exvehicletype vehicletype, " +
        " COUNT(*) vehiclecount, " +
        "'" + acctDate + "' as statisDay " +
        " FROM " +
        " (SELECT " +
        " distinct a.passid, " +
        " a.entollstationid, " +
        " b.tollstationname " +
        " FROM " +
        " ods.ods_etc_tollenbillinfo a " +
        " LEFT JOIN " +
        " tmp_station_info b " +
        " ON a.entollstationid = b.gbtollstationid " +
        " WHERE "
        + enTime +
        " AND b.tollstationname NOT LIKE '%九江一桥%' " +
        " AND a.transCode in ('0101','0103','0106')) a " +
        " JOIN " +
        " (SELECT " +
        " distinct a.passid, " +
        " a.extollstationid, " +
        " a.exvehicletype, " +
        " b.tollstationname " +
        " FROM " +
        " ods.ods_etc_tollexbillinfo a " +
        " LEFT JOIN " +
        " tmp_station_info b " +
        " ON a.extollstationid = b.gbtollstationid " +
        " WHERE "
        + exTime +
        " AND b.tollstationname NOT LIKE '%九江一桥%' " +
        " AND a.transCode in ('0201','0203','0206')) b " +
        " ON " +
        " a.passid = b.passid " +
        " GROUP BY a.entollstationid,a.tollstationname,b.extollstationid,b.tollstationname,b.exvehicletype " +
        " UNION " +
        " SELECT " +
        " a.gantryid enid, " +
        " a.gantryname enname, " +
        " 'gantry' entype, " +
        " b.extollstationid stationid, " +
        " b.tollstationname stationname, " +
        " b.exvehicletype vehicletype, " +
        " COUNT(*) vehiclecount, " +
        "'" + acctDate + "' as statisDay " +
        " FROM " +
        " (SELECT " +
        " distinct a.passid, " +
        " a.gantryid, " +
        " b.gantryname " +
        " FROM " +
        " ods.ods_etc_gantryetcbill a " +
        " LEFT JOIN " +
        " tmp_organization_gantryinfo b " +
        " ON a.gantryid = b.gantryid " +
        " WHERE "
        + enTime +
        " AND a.gantrytype = '2' " +
        " AND ((a.tradeResult='0' and a.mediaType='2') or (a.OBUTradeResult='0' and a.mediaType='1'))) a " +
        " JOIN " +
        " (SELECT " +
        " distinct a.passid, " +
        " a.extollstationid, " +
        " a.exvehicletype, " +
        " b.tollstationname " +
        " FROM " +
        " ods.ods_etc_tollexbillinfo a " +
        " LEFT JOIN " +
        " tmp_station_info b " +
        " ON a.extollstationid = b.gbtollstationid " +
        " WHERE "
        + exTime +
        " AND b.tollstationname NOT LIKE '%九江一桥%' " +
        " AND a.transCode in ('0201','0203','0206')) b " +
        " ON " +
        " a.passid = b.passid " +
        " GROUP BY a.gantryid,a.gantryname,b.extollstationid,b.tollstationname,b.exvehicletype "
    )
  }
}
